SUBCONSULTAS

antes de comezar recuerda el:
tee d:facturacion.txt

1.Create database facturacion;

- Use facturación;

2.Vamos a crear las tablas, pOr ejemplo crearemos la tabla cliente 
Create table cliente
(cedula char(10) not null primary key,
Nombre char(50) not  null,
Direccion char(50) not null,
Fechainicio date not null);

3.Insertar la información, para el ejemplo insertamos un cliente
Insert into cliente(cedula,nombre,dirección,fechainicio) values(‘100’,’Alexandra Lopez’,’Aranjuez’,’2003-12-03’);
insert into CLIENTE(cedula,nombre,direcciOn,fechainicio) values('200','sebastian perez ','sabaneta','2005-07-17');
insert into CLIENTE(cedula,nombre,direcciOn,fechainicio) values('300','sara herrera','floresta','2007-09-20');
insert into CLIENTE(cedula,nombre,direcciOn,fechainicio) values('400','luisa roldan','floresta','2009-10-15');
insert into CLIENTE(cedula,nombre,direcciOn,fechainicio) values('500','mario henao','bello','2004-02-28');
insert into CLIENTE(cedula,nombre,direcciOn,fechainicio) values('600','luisa cifuentes','florencia','2003-09-01');
insert into CLIENTE(cedula,nombre,direcciOn,fechainicio) values('700','edith garcia','envigado','2006-03-03');
insert into CLIENTE(cedula,nombre,direcciOn,fechainicio) values('800','claudia lainez','itag','2005-04-13');
insert into CLIENTE(cedula,nombre,direcciOn,fechainicio) values('900','vivian piedrahita','centro','2006-01-19');

- CREATE TABLE FACTURA
(nrofactura char(20) not null primary key,
cedula char(20) not null,
subtotal integer not null,
iva double not null,
retencion double not null,
total double not null,
foreign key (cedula) references CLIENTE(cedula) on delete cascade on update cascade
)engine=innodb;

insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('1001','100',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('2002','300',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('3003','200',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('4004','400',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('5005','200',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('6006','500',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('7007','100',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('8008','400',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('9009','400',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('1101','600',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('2202','700',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('3303','600',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('4404','100',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('5505','700',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('6606','800',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('7707','900',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('8808','800',0,0,0,0);
insert into FACTURA(nrofactura,cedula,subtotal,iva,retencion,total) values('9909','900',0,0,0,0);

- CREATE TABLE PRODUCTO
(codigo char(20) not null,
articulo char(40) not null,
valorunitario double not null,
cantidad integer not null,
valorventa double not null,
existencia integer not null
)engine=innodb;

insert into PRODUCTO(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values('10','tv',1250000,22,0,0);
insert into PRODUCTO(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values('20','auricules',75000,27,0,0);
insert into PRODUCTO(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values('30','mp3',150000,24,0,0);
insert into PRODUCTO(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values('40','mouse',25000,33,0,0);
insert into PRODUCTO(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values('50','taclado',130000,45,0,0);
insert into PRODUCTO(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values('60','disco duro',203000,17,0,0);
insert into PRODUCTO(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values('70','unidad dvd',250000,19,0, 0);
insert into PRODUCTO(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values('80','usb',80000,28,0,0);
insert into PRODUCTO(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values('90','lapiz optico',134000,17,0,0);

- CREATE TABLE DETALLEFACTURA
(id char(20) not null primary key,
nrofactura char(20) not null,
codigo char(20) not null,
cantidad integer not null,
valor double not null,
total double not null,
foreign key (nrofactura) references FACTURA(nrofactura) on delete cascade on update cascade
)engine=innodb;

insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('1','1001','10',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('2','1001','40',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('3','1001','70',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('4','2002','60',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('5','3003','20',4,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('6','3003','80',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('7','4004','10',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('8','4004','20',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('9','4004','30',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('10','4004','60',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('11','4004','70',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('12','4004','80',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('13','5005','10',3,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('14','6006','20',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('15','6006','80',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('16','6006','30',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('17','6006','90',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('18','6006','50',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('19','6006','40',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('20','7007','10',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('21','7007','90',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('22','8008','60',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('23','8008','40',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('24','8008','50',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('25','9009','50',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('26','9009','80',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('27','1101','30',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('28','2202','30',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('29','1101','60',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('30','3303','60',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('31','4404','90',3,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('32','5505','80',4,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('33','6606','70',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('34','7707','10',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('35','8808','40',2,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('36','9909','20',3,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('37','9909','30',4,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('38','6606','50',5,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('39','7707','40',1,0,0);
insert into DETALLEFACTURA(id,nrofactura,codigo,cantidad,valor,total) values('40','1101','30',1,0,0);


4.Calcular valorventa aumentando un 17% del valorunitario

Update producto set valorventa=(select valorunitario +(valorunitario*0.17) );


5.Llevar el valorventa a valor de detallefactura segun artículo comprado
update detallefactura set valor=(select valorventa  from producto where producto.codigo=detallefactura.codigo);


6.Calcular el total en la tabla detallefactura (cantidad x valor)

Update detallefactura  set total=(cantidad*valor);


7.Calcular cual es el subtotal de cada factura y guardarlo en el campo respectivo
Update factura set subtotal=(select sum(total) from detallefactura where detallefactura.nrofactura=factura.nrofactura);


8.Calcular el iva (16% del subtotal), la retención (3.5% del subtotal) y el total(subtotal + iva - retención)
-Update factura set  iva=(subtotal*0.16);
-Update factura set retencion=(subtotal*0.3);
-Update factura set total=((subtotal +iva)-retencion);


9.Calcular cuales son las existencias de cada articulo
Update producto set existencia=cantidad-(select sum(cantidad) from detallefactura where detallefactura.codigo=producto.codigo);


10.Cuantas facturas tiene cada cliente
Select nombre, count(nombre) as facturas from factura,cliente where factura.cedula=cliente.cedula group by nombre;


11.Listar el total(suma) de ventas de cada producto 
select articulo, sum(total) as 'total de ventas' from detallefactura,producto where detallefactura.codigo=producto.codigo group by articulo;


12.Listar las facturas y el valor de cada una segun las compra de alexandra lopez
select cedula,nrofactura,total from factura where factura.cedula=(select cedula from cliente where nombre='alexandra lopez');


13.Crear una tabla detallado dentro de la bd subconsultas con los campos

id auto incremento
cedula
nrofactura
totalarticulos
valortotal

create table detallado (id int(4) not null auto_increment, cedula int(10) not null, nrofactura int(4) not null, totalarticulos int(4) not null, valortotal int(15) not null, primary key (id));


14.crear una subconsulta para ingresar estos datos a la tabla detallado

cedula del cliente
numero de facturas del cliente
suma  de los articulos de cada factura
total pagado de cada factura

insert into detallado select null,cedula,nrofactura,subtotal,total from factura;


15.eliminar las facturas de los clientes que hayan iniciado vinculo con la empresa antes de 2004
delete from detallado where cedula=(select cedula from cliente where year(fechainicio)<2004 and detallado.cedula=cliente.cedula);


POR ULTIMO RECUERDA HACER UN BACKUP DE LA BASE DE DATOS DE LA SIGUIENTE MANERA:
mysqldump –B –uroot –p facturacion1>d:/facturacion1.sql